package org.jeecg.modules.online.cgreport.service.impl;

import cn.hutool.core.util.ReUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.constant.DataBaseConstant;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.dynamic.db.DataSourceCachePool;
import org.jeecg.common.util.dynamic.db.DynamicDBUtil;
import org.jeecg.common.util.dynamic.db.SqlUtils;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.cgreport.def.CgReportConstant;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportHead;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportItem;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportParam;
import org.jeecg.modules.online.cgreport.mapper.OnlCgreportHeadMapper;
import org.jeecg.modules.online.cgreport.model.OnlCgreportModel;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportHeadService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportItemService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportParamService;
import org.jeecg.modules.online.cgreport.util.CgReportQueryParamUtil;
import org.jeecg.modules.online.cgreport.util.SqlUtil;
import org.jeecg.modules.online.config.util.DbTableUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Description: 在线报表配置
 * @Author: jeecg-boot
 * @Date: 2019-03-08
 * @Version: V1.0
 */
@Service("onlCgreportHeadServiceImpl")
@Slf4j
public class OnlCgreportHeadServiceImpl extends ServiceImpl<OnlCgreportHeadMapper, OnlCgreportHead> implements IOnlCgreportHeadService {

	@Autowired
	private IOnlCgreportParamService onlCgreportParamService;
	@Autowired
	private IOnlCgreportItemService onlCgreportItemService;
	@Autowired
	private OnlCgreportHeadMapper mapper;
	@Autowired
	private ISysBaseAPI sysBaseAPI;

	/**
	 * 执行 select sql语句
	 *
	 * @param sql
	 */
	@Override
	public Map<String, Object> executeSelectSql(String sql, String onlCgreportHeadId, Map<String,Object> params) throws SQLException {
		String dbType = sysBaseAPI.getDatabaseType();
		//1.先处理sql中的参数
		LambdaQueryWrapper<OnlCgreportParam> paramQuery = new LambdaQueryWrapper<OnlCgreportParam>();
		paramQuery.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
    	List<OnlCgreportParam> paramList = onlCgreportParamService.list(paramQuery);
    	if(paramList!=null && paramList.size()>0) {
    		for (OnlCgreportParam p : paramList) {
    			//前端传值为了避免 SQL自带的参数和查询条件参数弄混了 故而将SQL自带的参数名加前缀:self_
    			Object value = params.get("self_"+p.getParamName());
    			String valueStr ="";
    			if(value!=null) {
    				valueStr = value.toString();
    			}else if(value==null && oConvertUtils.isNotEmpty(p.getParamValue())) {
    				valueStr = p.getParamValue();
    			}
    			sql = sql.replace("${"+p.getParamName()+"}", valueStr);
			}
    	}
		Map<String, Object> result = new HashMap<>();
		//2.分页查询逻辑
		Integer pageSize = oConvertUtils.getInt(params.get("pageSize"),10);
		Integer pageNo = oConvertUtils.getInt(params.get("pageNo"),1);
		Page<Map<String,Object>> page = new Page<>(pageNo,pageSize);
		//3.获取查询条件sql
		LambdaQueryWrapper<OnlCgreportItem> query = new LambdaQueryWrapper<OnlCgreportItem>();
		query.eq(OnlCgreportItem::getCgrheadId,onlCgreportHeadId);
		query.eq(OnlCgreportItem::getIsSearch, 1);
		List<OnlCgreportItem> fieldList = onlCgreportItemService.list(query);

		String alias = "jeecg_rp_temp.";
		String condition = CgReportQueryParamUtil.getListQueryConditionSql(fieldList, params,alias);

		//check sqlserver 不允许sql内含 order by排序片段
		if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
			if (DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(dbType)) {
				throw new JeecgBootException("SqlServer不支持SQL内排序!");
			}
		}

		String querySql = "select * from ("+sql+") jeecg_rp_temp "+" where 1=1 "+condition;
		//加载数据权限配置 和 系统内置变量
		querySql = SqlUtil.rewriteReportSql(querySql);
		Object orderColumn = params.get("column");
		if(orderColumn!=null) {
			querySql+= " order by jeecg_rp_temp."+orderColumn.toString()+" "+params.get("order").toString();
		}
		//4.执行查询
		log.info("报表查询sql=>\r\n"+querySql);
		IPage<Map<String,Object>> pageList = this.mapper.selectPageBySql(page, querySql);
		result.put("total", pageList.getTotal());
		//此处无论什么数据库，都把查询结果key转为小写
		result.put("records",CgformUtil.toLowerCasePageList(pageList.getRecords()));
		return result;
	}

    /**
     * 执行 select sql语句
     *
     * @param sql
     */
    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> executeSelectSqlDynamic(String dbKey, String sql, Map<String, Object> params, String onlCgreportHeadId) {
        DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);

		String order = (String) params.get("order");
		String column = (String) params.get("column");
        int pageNo = oConvertUtils.getInt(params.get("pageNo"), 1);
        int pageSize = oConvertUtils.getInt(params.get("pageSize"), 10);
		log.info("【Online多数据源逻辑】报表查询参数params: " + JSON.toJSONString(params));


		//1.先处理sql中的参数
		LambdaQueryWrapper<OnlCgreportParam> paramQuery = new LambdaQueryWrapper<OnlCgreportParam>();
		paramQuery.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
		List<OnlCgreportParam> paramList = onlCgreportParamService.list(paramQuery);
		if(paramList!=null && paramList.size()>0) {
			for (OnlCgreportParam p : paramList) {
				//前端传值为了避免 SQL自带的参数和查询条件参数弄混了 故而将SQL自带的参数名加前缀:self_
				Object value = params.get("self_"+p.getParamName());
				String valueStr ="";
				if(value!=null) {
					valueStr = value.toString();
				}else if(value==null && oConvertUtils.isNotEmpty(p.getParamValue())) {
					valueStr = p.getParamValue();
				}
				sql = sql.replace("${"+p.getParamName()+"}", valueStr);
			}
		}

		//2.获取页面查询条件 和 排序逻辑sql
		LambdaQueryWrapper<OnlCgreportItem> query = new LambdaQueryWrapper<OnlCgreportItem>();
		query.eq(OnlCgreportItem::getCgrheadId,onlCgreportHeadId);
		query.eq(OnlCgreportItem::getIsSearch, 1);
		List<OnlCgreportItem> fieldList = onlCgreportItemService.list(query);

		//check sqlserver 不允许sql内含 order by排序片段
		if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
			if ("3".equalsIgnoreCase(dbSource.getDbType())) {
				throw new JeecgBootException("SqlServer不支持SQL内排序!");
			}
		}

		String alias = "jeecg_rp_temp.";
		String condition = CgReportQueryParamUtil.getListQueryConditionSql(fieldList, params,alias);
		String querySql = "select * from ("+sql+") jeecg_rp_temp "+" where 1=1 "+condition;
		//加载数据权限配置 和 系统内置变量
		querySql = SqlUtil.rewriteReportSql(querySql);

		// 获取查询总记录数SQL
		String countSql = SqlUtils.getCountSql(querySql);

		//排序逻辑
		Object orderColumn = params.get("column");
		if(orderColumn!=null) {
			querySql+= " order by jeecg_rp_temp."+orderColumn.toString()+" "+params.get("order").toString();
		}

        //3.获取分页SQL
        String pageSQL = SqlUtils.createPageSqlByDBType(dbSource.getDbType(), querySql, pageNo, pageSize);

		//4.执行查询
		log.info("多数据源 报表查询sql=>querySql: " + querySql);
		log.info("多数据源 报表查询sql=>pageSQL: " + pageSQL);
		log.info("多数据源 报表查询sql=>countSql: " + countSql);

        Map<String, Object> result = new HashMap<>();
        // 查询总数
        Map<String, Object> queryCount = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, countSql);
        result.put("total", queryCount.get("total"));
        // 查询分页数据
        List<Map<String, Object>> pageList = DynamicDBUtil.findList(dbKey, pageSQL);
        result.put("records", CgformUtil.toLowerCasePageList(pageList));
        return result;
    }

	/** 修改全部项，包括新增、修改、删除 */
	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> editAll(OnlCgreportModel values) {
		OnlCgreportHead head = values.getHead();

		OnlCgreportHead onlCgreportHeadEntity = super.getById(head.getId());
		if (onlCgreportHeadEntity == null) {
			return Result.error("未找到对应实体");
		}

		super.updateById(head);

		//TODO 需要改造
		// update-begin--Author:sunjianlei Date:20190912 for：修复sql解析后数据保存失败的问题
		// 先删除所有子项
		LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
		queryWrapper1.eq(OnlCgreportItem::getCgrheadId, head.getId());
		onlCgreportItemService.remove(queryWrapper1);

		LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
		queryWrapper2.eq(OnlCgreportParam::getCgrheadId, head.getId());
		onlCgreportParamService.remove(queryWrapper2);

        // 再插入子项
        for (OnlCgreportParam param : values.getParams()) {
            param.setCgrheadId(head.getId());
        }
        for (OnlCgreportItem item : values.getItems()) {
        	//字段默认保存为小写(为了兼容各种数据库)
        	item.setFieldName(item.getFieldName().trim().toLowerCase());
            item.setCgrheadId(head.getId());
        }

        onlCgreportItemService.saveBatch(values.getItems());
        onlCgreportParamService.saveBatch(values.getParams());
        return Result.ok("全部修改成功");
		// update-end--Author:sunjianlei Date:20190912 for：修复sql解析后数据保存失败的问题
	}


	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> delete(String id) {
		boolean ok = super.removeById(id);
		if (ok) {
			//删除子表数据
			LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
			queryWrapper1.eq(OnlCgreportItem::getCgrheadId, id);
			onlCgreportItemService.remove(queryWrapper1);

			LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
			queryWrapper2.eq(OnlCgreportParam::getCgrheadId, id);
			onlCgreportParamService.remove(queryWrapper2);
		}
		return Result.ok("删除成功");
	}

	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> bathDelete(String[] ids) {
		for(String id:ids){
			boolean ok = super.removeById(id);
			if (ok) {
				//删除子表数据
				LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
				queryWrapper1.eq(OnlCgreportItem::getCgrheadId, id);
				onlCgreportItemService.remove(queryWrapper1);

				LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
				queryWrapper2.eq(OnlCgreportParam::getCgrheadId, id);
				onlCgreportParamService.remove(queryWrapper2);
			}
		}
		return Result.ok("删除成功");
	}

	/**
	 * 动态数据源： 获取SQL解析的字段
	 */
	@Override
	public List<String> getSqlFields(String sql,String dbKey) throws SQLException {
		List<String> fields = null;
		if(StringUtils.isNotBlank(dbKey)){
			fields = this.parseSql(sql,dbKey);
		}else{
			fields = this.parseSql(sql,null);
		}
		return fields;
	}


	/**
	 * 解析SQL参数
	 */
	@Override
	public List<String> getSqlParams(String sql) {
		if(oConvertUtils.isEmpty(sql)){
			return null;
		}
		List<String> params = new ArrayList<String>();
		String regex = "\\$\\{\\w+\\}";

		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sql);
		while(m.find()){
			String whereParam = m.group();
			params.add(whereParam.substring(whereParam.indexOf("{")+1,whereParam.indexOf("}")));
		}
		return params;
	}

	/**
	 * 动态数据源： 获取SQL解析的字段
	 * @param sql
	 * @param dbKey
	 * @return
	 */
	private List<String> parseSql(String sql,String dbKey) throws SQLException {
		if(oConvertUtils.isEmpty(sql)){
			return null;
		}
		//update-begin-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败
		//step.1 去掉结尾的;和空格
		sql = sql.trim();
		if (sql.endsWith(";")) {
			sql = sql.substring(0, sql.length() - 1);
		}

		//step2. 替换sql中的上下文变量
		sql = QueryGenerator.convertSystemVariables(sql);

		//step3. 判断sql中是否存在${}参数格式
		sql = SqlUtil.getUniversalSql(sql);
		//update-end-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败

		//step.4 判断数据源类型，写不同的分页SQL（只查询一条，提高查询效率）
		Set<String> fieldsSet;
		if (StringUtils.isNotBlank(dbKey)) {
			log.info("parse sql : "+sql);
			DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
			// check sqlserver 不允许sql内含 order by排序片段
			if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
				if ("3".equalsIgnoreCase(dbSource.getDbType())) {
					throw new JeecgBootException("SqlServer不支持SQL内排序!");
				}
			}

			// TODO  Postger 未实现仅查询一条数据
			//组装不同数据库，分页SQL
			if (DataBaseConstant.DB_TYPE_MYSQL_NUM.equals(dbSource.getDbType())) {
				sql = "SELECT * FROM (" + sql + ") temp LIMIT 1";
			} else if (DataBaseConstant.DB_TYPE_ORACLE_NUM.equals(dbSource.getDbType())) {
				sql = "SELECT * FROM (" + sql + ") temp WHERE ROWNUM <= 1";
			} else if (DataBaseConstant.DB_TYPE_SQLSERVER_NUM.equals(dbSource.getDbType())) {
				sql = "SELECT TOP 1 * FROM (" + sql + ") temp";
			}
			log.info("parse sql with page : "+sql);
			Map<String, Object> objectMap = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, sql);
			if (objectMap == null) {
				throw new JeecgBootException("该报表sql没有数据");
			}
			fieldsSet = objectMap.keySet();
		} else {
			log.info("parse sql: "+sql);
			// check sqlserver 不允许sql内含 order by排序片段
			if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
				if (DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(sysBaseAPI.getDatabaseType())) {
					throw new JeecgBootException("SqlServer不支持SQL内排序!");
				}
			}
			//组装分页SQL
			IPage<Map<String, Object>> pageList = this.mapper.selectPageBySql(new Page<>(1, 1), sql);
			List<Map<String, Object>> result = pageList.getRecords();
			if (result.size() < 1) {
				throw new JeecgBootException("该报表sql没有数据");
			}
			fieldsSet = result.get(0).keySet();
		}

		//sql解析字段，去掉 Oracle数据库默认关键词 ROW_ID
		if (fieldsSet != null) {
			fieldsSet.remove("ROW_ID");
		}
		return new ArrayList<>(fieldsSet);
	}

	@Override
	public Map<String, Object> queryCgReportConfig(String reportId) {
		Map<String,Object> cgReportM = new HashMap<String, Object>(0);
		Map<String,Object> mainM = mapper.queryCgReportMainConfig(reportId);
		List<Map<String,Object>> itemsM = mapper.queryCgReportItems(reportId);
        List<OnlCgreportParam> params = mapper.queryCgReportParams(reportId);
		//update-begin-author:taoyan date:20190930 for:oracle数据库 online报表导出异常
		if(DbTableUtil.hasLetterCaseProblem()){
			cgReportM.put(CgReportConstant.MAIN, CgformUtil.transLowerCaseMapKey(mainM));
			cgReportM.put(CgReportConstant.ITEMS, CgformUtil.toLowerCasePageList(itemsM));
		}else{
			cgReportM.put(CgReportConstant.MAIN, mainM);
			cgReportM.put(CgReportConstant.ITEMS,itemsM);
		}
		//update-end-author:taoyan date:20190930 for:oracle数据库 online报表导出异常
		cgReportM.put(CgReportConstant.PARAMS, params);
		return cgReportM;
	}

	/**
	 * 分页查询报表数据
	 */
	@Override
	public List<Map<?, ?>> queryByCgReportSql(String sql, Map params,Map paramData,
			int pageNo, int pageSize) {
		String querySql = SqlUtil.getFullSql(sql,params);
		List<Map<?, ?>> result = null;
		// TODO：这里的作用是什么？如果没用就删了
		if(paramData!=null&&paramData.size()==0){
			paramData = null;
		}
		if(pageNo==-1 && pageSize==-1){
			result = mapper.executeSelete(querySql);
		}else{
			Page<Map<String,Object>> page = new Page<>(pageNo,pageSize);
			IPage<Map<String,Object>> pageList = this.mapper.selectPageBySql(page, querySql);
			if(pageList.getRecords()!=null && pageList.getRecords().size()>0) {
				result.addAll(pageList.getRecords());
			}
		}
		return result;
	}
}
